﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;

namespace QuanLyDeTaiKHCN.Obj_class
{
    public class CatalogAccess
    {
        #region "Quan ly tai khoan"
        /// <summary>
        /// Trường hợp có thay đổi mật khẩu
        /// </summary>
        /// <param name="username"></param>
        /// <param name="pass"></param>
        /// <param name="grant"></param>
        /// <returns></returns>
        public static bool UpdateAcount(string username,string pass, bool grant)
        {
            _dbDataContext _db = new _dbDataContext();
            var val = _db.tblAcounts.Single(r => r.TenDangNhap.Trim().ToUpper() == username.ToUpper());
            val.MatKhau = pass;
            val.PhanQuyen = grant;
            try
            {
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        /// <summary>
        /// Trường hợp không thay đổi mật khẩu
        /// </summary>
        /// <param name="username"></param>
        /// <param name="grant"></param>
        /// <returns></returns>
        public static bool UpdateAcount(string username, bool grant)
        {
            _dbDataContext _db = new _dbDataContext();
            var val = _db.tblAcounts.Single(r => r.TenDangNhap.Trim().ToUpper() == username.ToUpper());
            val.PhanQuyen = grant;
            try
            {
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        public static bool InsertAcount(string tenhienthi, string tendangnhap, string matkhau, bool grant)
        {
            _dbDataContext _db = new _dbDataContext();
            tblAcount _tb = new tblAcount();
            _tb.TenHienThi = tenhienthi;
            _tb.TenDangNhap = tendangnhap;
            _tb.MatKhau = matkhau;
            _tb.PhanQuyen = grant;
            try
            {
                _db.tblAcounts.InsertOnSubmit(_tb);
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        public static bool DeleteAcount(int id)
        {
            _dbDataContext _db = new _dbDataContext();
            var val = _db.tblAcounts.Single(r => r.ID==id);
            try
            {
                _db.tblAcounts.DeleteOnSubmit(val);
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        /// <summary>
        /// Kiểm tra xem tên đăng nhập đã có chưa, nếu true tức là đã tồn tại rồi
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public static bool KiemTraTrungTenDangNhap(string username)
        {
            _dbDataContext _db = new _dbDataContext();
            var val = _db.tblAcounts.Where(r => r.TenDangNhap.Trim().ToUpper() == username.ToUpper());
            return val.Count() > 0;
        }
        public static int GetUserID(string username)
        {
            _dbDataContext _db=new _dbDataContext();
            return _db.tblAcounts.Single(r => r.TenDangNhap.Trim().ToUpper() == username.ToUpper()).ID;
        }
        #endregion
        #region "Phần quản lý lĩnh vực"
        /// <summary>
        /// Chèn dữ liệu vào bảng danh mục
        /// </summary>
        /// <param name="tenlinhvuc">Tên danh mục</param>
        /// <param name="loai">1: lĩnh vực khoa học công nghệ, 2: Học hàm học vị, 3: chức vụ</param>
        /// <returns></returns>
        public static bool InsertDanhMuc(string tenlinhvuc,int loai)
        {
            _dbDataContext _db = new _dbDataContext();
            tblDanhMuc _tb = new tblDanhMuc();
            _tb.TenDanhMuc = tenlinhvuc;
            _tb.Loai = loai;
            try
            {
                _db.tblDanhMucs.InsertOnSubmit(_tb);
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        public static bool UpdateDanhMuc(int id,string tenlinhvuc)
        {
            _dbDataContext _db = new _dbDataContext();
            var _tb = _db.tblDanhMucs.Single(r => r.ID == id);
            _tb.TenDanhMuc = tenlinhvuc;
            try
            {
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        public static bool DeleteDanhMuc(int id)
        {
            _dbDataContext _db = new _dbDataContext();
            var _tb = _db.tblDanhMucs.Single(r => r.ID == id);
            try
            {
                _db.tblDanhMucs.DeleteOnSubmit(_tb);
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        #endregion
        #region "Quản lý thành viên hội đồng"
        public static void BindDatatoDropList(int loai,DropDownList drl)
        {
            ListItem item = new ListItem("---Chọn---", "-1");
            drl.Items.Add(item);
            _dbDataContext _db = new _dbDataContext();
            var list = _db.tblDanhMucs.Where(r => r.Loai == loai).ToList();
            foreach (var items in list)
            {
                item = new ListItem(items.TenDanhMuc.Trim(), items.ID.ToString());
                drl.Items.Add(item);
            }
        }
        public static void BindDSHoiDong(DropDownList drl)
        {
            ListItem item = new ListItem("---Chọn---", "-1");
            drl.Items.Add(item);
            _dbDataContext _db = new _dbDataContext();
            var list = _db.tblHoiDongKhoaHocs.ToList();
            foreach (var items in list)
            {
                item = new ListItem(items.tblDanhMuc.TenDanhMuc.Trim()+":"+ items.HoTen.Trim(), items.ID.ToString());
                drl.Items.Add(item);
            }
        }
        public static bool InsertMember(string hoten,bool phai,int hochamhocvi,int chucvu,int linhvuc,string noicongtac,string diachi,string dienthoai,string email)
        {
            _dbDataContext _db = new _dbDataContext();
            tblHoiDongKhoaHoc _tb = new tblHoiDongKhoaHoc();
            _tb.HoTen = hoten;
            _tb.Phai = phai;
            _tb.HocHamHocVi = hochamhocvi;
            _tb.ChucVu = chucvu;
            _tb.LinhVuc = linhvuc;
            _tb.NoiCongTac = noicongtac;
            _tb.DiaChi=diachi;
            _tb.DienThoai=dienthoai;
            _tb.Email=email;
            try{
                _db.tblHoiDongKhoaHocs.InsertOnSubmit(_tb);
                _db.SubmitChanges();
                return true;
            }catch{return false;}
        }
        public static bool UpdateMember(int id,string hoten,bool phai, int hochamhocvi, int chucvu, int linhvuc, string noicongtac, string diachi, string dienthoai, string email)
        {
            _dbDataContext _db = new _dbDataContext();
            var _tb = _db.tblHoiDongKhoaHocs.Single(r => r.ID == id);
            _tb.HoTen = hoten;
            _tb.Phai = phai;
            _tb.HocHamHocVi = hochamhocvi;
            _tb.ChucVu = chucvu;
            _tb.LinhVuc = linhvuc;
            _tb.NoiCongTac = noicongtac;
            _tb.DiaChi = diachi;
            _tb.DienThoai = dienthoai;
            _tb.Email = email;
            try
            {
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        public static bool DeleteMember(int id)
        {
            _dbDataContext _db = new _dbDataContext();
            var _tb = _db.tblHoiDongKhoaHocs.Single(r => r.ID == id);
            try
            {
                _db.tblHoiDongKhoaHocs.DeleteOnSubmit(_tb);
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        #endregion
        #region "Quan ly danh sach de tai"
        public static string CreateMaDeTai()
        {
            _dbDataContext _db = new _dbDataContext();
            int day = DateTime.Now.Day;
            int month = DateTime.Now.Month;
            string par = (day<10?"0"+day.ToString():day.ToString()) + (month<10?"0"+month.ToString():month.ToString()) + DateTime.Now.Year.ToString();
            var list = _db.tblDanhSachDeTaiDuAns.Where(r => r.MaDeTaiDuAn.Substring(0, 8) == par).ToList();
            if (list.Count() == 0)
            {
                return par + "01";
            }
            else
            {
                List<int> listint = new List<int>();
                foreach (var item in list)
                {
                    listint.Add(int.Parse(item.MaDeTaiDuAn.Substring(8)));
                }
                int max = listint.Max()+1;
                string st = "";
                if (max >= 10)
                    st = max.ToString();
                else
                    st = "0" + max.ToString();
                return par + st;
            }
        }
        public static DateTime ConvertToDate(string datetime)
        {
            try
            {
                int year = int.Parse(datetime.Split('/')[2]);
                int month = int.Parse(datetime.Split('/')[1]);
                int day = int.Parse(datetime.Split('/')[0]);
                DateTime date = new DateTime(year, month, day);
                return date;
            }
            catch { return DateTime.Now; }
        }
        public static bool ConvertToDateBool(string datetime)
        {
            try
            {
                int year = int.Parse(datetime.Split('/')[2]);
                int month = int.Parse(datetime.Split('/')[1]);
                int day = int.Parse(datetime.Split('/')[0]);
                DateTime date = new DateTime(year, month, day);
                return true;
            }
            catch { return false; }
        }
        public static bool CreateDeTai(string madetai,int linhvuc, string tendetai, int loai, int loaihoso,int nguoiquanly,string thoigianbanhanhdanhmuc)
        {
            _dbDataContext _db = new _dbDataContext();
            tblDanhSachDeTaiDuAn _tb = new tblDanhSachDeTaiDuAn();
            _tb.MaDeTaiDuAn = madetai;
            _tb.TenDeTaiDuAn = tendetai;
            _tb.Loai = loai;
            _tb.LoaiHoSo = loaihoso;
            _tb.NgayKhoiTaoDeTai = DateTime.Now;
            _tb.NguoiQuanLy = nguoiquanly;
            _tb.LinhVuc = linhvuc;
            _tb.ThoiGianBanHanhDanhMuc = thoigianbanhanhdanhmuc;
            try
            {
                _db.tblDanhSachDeTaiDuAns.InsertOnSubmit(_tb);
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        public static bool CapNhatThongTinDeTai(string madetaiduan, int loai,int linhvuc, string tendetaiduan, string thoigianthuchientu, string thoigianthuchienden,
            int tongkinhphi, int tungansach, int nguonvonkhac, string thuocchuongtrinh, string chunhiemdetai, int hochamhocvi, string dienthoaicoquan,
            string dienthoainharieng, string dienthoaididong, string email, string tendonvichutri, string diachidonvi, string dienthoaidonvi, string faxdonvi,
            int loaihoso, int nguoiquanly,string thoigianbanhanhdanhmuc)
        {
            _dbDataContext _db = new _dbDataContext();
            var _tb = _db.tblDanhSachDeTaiDuAns.Single(r => r.MaDeTaiDuAn.Trim() == madetaiduan);
            _tb.Loai = loai;
            _tb.LinhVuc = linhvuc;
            _tb.TenDeTaiDuAn = tendetaiduan;
            _tb.ThoiGianThucHienTu = ConvertToDate(thoigianthuchientu);
            _tb.ThoiGianThcuHienTuSo = _db.Tinhsongay(ConvertToDate(thoigianthuchientu));
            _tb.ThoiGianThucHienDen = ConvertToDate(thoigianthuchienden);
            _tb.ThoiGianThucHienDenSo = _db.Tinhsongay(ConvertToDate(thoigianthuchienden));
            _tb.TongKinhPhi = tongkinhphi;
            _tb.TuNganSach = tungansach;
            _tb.NguonVonKhac = nguonvonkhac;
            _tb.ThuocChuongTrinh = thuocchuongtrinh;
            _tb.ChuNhiemDeTai = chunhiemdetai;
            _tb.HocHamHocVi = hochamhocvi;
            _tb.DienThoaiCoQuan=dienthoaicoquan;
            _tb.DienThoaiNhaRieng = dienthoainharieng;
            _tb.DienThoaiDiDong = dienthoaididong;
            _tb.Email = email;
            _tb.TenDonViChuTri = tendonvichutri;
            _tb.DiaChiDonVi = diachidonvi;
            _tb.DienThoaiDonVi = dienthoaidonvi;
            _tb.FaxDonVi = faxdonvi;
            _tb.LoaiHoSo = loaihoso;
            _tb.NguoiQuanLy = nguoiquanly;
            _tb.ThoiGianBanHanhDanhMuc = thoigianbanhanhdanhmuc;
            try
            {
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }

        }
        public static bool InsertHoiDong(string madetaiduan, string hoidong,string soquyetdinh,string ngayquyetdinh,int quyetdinhcua,string soquyetdinhthanhlaphoidong,string ngayquyetdinhthanhlaphoidong)
        {
            _dbDataContext _db = new _dbDataContext();
            tblThanhLapHoiDong _tb = new tblThanhLapHoiDong();
            _tb.MaDeTaiDuAn = madetaiduan;
            _tb.DanhSachHoiDong = hoidong;
            _tb.SoQuyetDinh = soquyetdinh;
            _tb.NgayQuyetDinh = ConvertToDate(ngayquyetdinh);
            _tb.QuyetDinhCua = quyetdinhcua;
            _tb.SoQuyetDinhThanhLap = soquyetdinhthanhlaphoidong;
            bool successconvert = ConvertToDateBool(ngayquyetdinhthanhlaphoidong);
            if (successconvert)
            {
                _tb.NgayQuyetDinhThanhLap = ConvertToDate(ngayquyetdinhthanhlaphoidong);
            }
            try
            {
                _db.tblThanhLapHoiDongs.InsertOnSubmit(_tb);
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        public static bool InsertHoiDongnghiemThu(string madetaiduan, string hoidong, string soquyetdinh, string ngayquyetdinh, int quyetdinhcua, string soquyetdinhthanhlaphoidong, string ngayquyetdinhthanhlaphoidong)
        {
            _dbDataContext _db = new _dbDataContext();
            tblThanhLapHoiDongNghiemThu _tb = new tblThanhLapHoiDongNghiemThu();
            _tb.MaDeTaiDuAn = madetaiduan;
            _tb.DanhSachHoiDong = hoidong;
            _tb.SoQuyetDinh = soquyetdinh;
            _tb.NgayQuyetDinh = ConvertToDate(ngayquyetdinh);
            _tb.QuyetDinhCua = quyetdinhcua;
            _tb.SoQuyetDinhThanhLap = soquyetdinhthanhlaphoidong;
            bool successconvert = ConvertToDateBool(ngayquyetdinhthanhlaphoidong);
            if (successconvert)
            {
                _tb.NgayQuyetDinhThanhLap = ConvertToDate(ngayquyetdinhthanhlaphoidong);
            }
            try
            {
                _db.tblThanhLapHoiDongNghiemThus.InsertOnSubmit(_tb);
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        public static bool UpdateHoiDong(string madetaiduan, string hoidong, string soquyetdinh, string ngayquyetdinh,  int quyetdinhcua, string soquyetdinhthanhlaphoidong, string ngayquyetdinhthanhlaphoidong)
        {
            _dbDataContext _db = new _dbDataContext();
            var _tb = _db.tblThanhLapHoiDongs.Single(r => r.MaDeTaiDuAn.Trim() == madetaiduan);
            _tb.DanhSachHoiDong = hoidong;
            _tb.SoQuyetDinh = soquyetdinh;
            _tb.NgayQuyetDinh = ConvertToDate(ngayquyetdinh);
            _tb.QuyetDinhCua = quyetdinhcua;
            _tb.SoQuyetDinhThanhLap = soquyetdinhthanhlaphoidong;
            bool successconvert = ConvertToDateBool(ngayquyetdinhthanhlaphoidong);
            if (successconvert)
            {
                _tb.NgayQuyetDinhThanhLap = ConvertToDate(ngayquyetdinhthanhlaphoidong);
            }
            try
            {
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        public static bool UpdateHoiDongNghiemThu(string madetaiduan, string hoidong, string soquyetdinh, string ngayquyetdinh, int quyetdinhcua, string soquyetdinhthanhlaphoidong, string ngayquyetdinhthanhlaphoidong)
        {
            _dbDataContext _db = new _dbDataContext();
            var _tb = _db.tblThanhLapHoiDongNghiemThus.Single(r => r.MaDeTaiDuAn.Trim() == madetaiduan);
            _tb.DanhSachHoiDong = hoidong;
            _tb.SoQuyetDinh = soquyetdinh;
            _tb.NgayQuyetDinh = ConvertToDate(ngayquyetdinh);
            _tb.QuyetDinhCua = quyetdinhcua;
            _tb.SoQuyetDinhThanhLap = soquyetdinhthanhlaphoidong;
            bool successconvert = ConvertToDateBool(ngayquyetdinhthanhlaphoidong);
            if (successconvert)
            {
                _tb.NgayQuyetDinhThanhLap = ConvertToDate(ngayquyetdinhthanhlaphoidong);
            }
            try
            {
                _db.SubmitChanges();
                return true;
            }
            catch { return false; }
        }
        #endregion
        #region "Thông tin khác"
        public static int GetLanGiaHan(string madetai)
        {
            _dbDataContext _db=new _dbDataContext();
            var list = _db.tblGiaHanDeTais.Where(r => r.MaDeTai.Trim() == madetai).ToList();
            if (list.Count() == 0)
                return 1;
            else
                return int.Parse(list.Max(r => r.Lan).ToString())+1;
        }
        public static int GetLanGhiChu(string madetai)
        {
            _dbDataContext _db = new _dbDataContext();
            var list = _db.tblGhiChus.Where(r => r.MaDeTai.Trim() == madetai).ToList();
            if (list.Count() == 0)
                return 1;
            else
                return int.Parse(list.Max(r => r.Lan).ToString()) + 1;
        }
        #endregion
    }
}